/**
 * Main
 */
package javaidiot;

import java.util.*;

/**
 *
 * @author Master
 */
public class Query {

    protected static View view = new View();
    private static final Scanner SN = new Scanner(System.in);


    /*
     * @return 
     */
    private static void out(String s) {
        System.out.println(s);
    }

    public static void main(String[] args) {
        //String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
        //String server = "jdbc:sqlserver://localhost:1433";
        String server = "jdbc:odbc:salnalex";
        //String props = ";integratedSecurity=false;database=javaidiot;username=javaidiot;password=javaidiot;";
        String props = "";
        //String dbQuery = "select EmployeeName, OrderID, PhoneNumber from Employees, OrderExecutor where Employees.EmployeeID = OrderExecutor.EmployeeID;";
        int year = 2011;
        String dbQuery = "SELECT Product.ProductName FROM Product WHERE ProductID IN ( SELECT distinct ProductID FROM OrderDetails JOIN Orders ON Orders.OrderID = OrderDetails.OrderID AND DATEPART(YYYY, Orders.OrderDate ) = " + year + " );;";

        SQLConnection db = new SQLConnection(driver, server, props);
        try {
            db.connect();
        } catch (Exception e) {
            out(e.getMessage());
        }

        // Controller part
        int a = -1;
        int a2 = -1;
        while (a != 0) {
            view.mainMenu();
            try {
                a = SN.nextInt();
            } catch (InputMismatchException e) {
                view.exception("not integer");
            }
            /*
             * Action list for top level menu
             */
            switch (a) {
                /*
                // get list of products ordered in certain month
                case 1:
                dbQuery = "SELECT Product.ProductName FROM Product WHERE ProductID IN ( SELECT distinct ProductID FROM OrderDetails JOIN Orders ON Orders.OrderID = OrderDetails.OrderID AND DATEPART(MM, Orders.OrderDate ) = " + view.askMonth() + " );";
                db.select(dbQuery);
                view.printArray(db.select.resultArray);
                 * 
                 */
                // Manage Products
                case 1:
                    while (a2 != 0) {
                        view.manageProducts();
                        try {
                            a2 = SN.nextInt();
                        } catch (InputMismatchException e) {
                            view.exception("not integer");
                        }
                        switch (a2) {
                            //add products;function_set name & set price
                            case 1:
                                view.productSetName();
                                String name = SN.nextLine();
                                if (SN.hasNext()) {
                                    String tmp = SN.next();
                                }
                                view.productSetPrice();
                                double price = -1f;
                                while (price < 0) {
                                    try {
                                        price = SN.nextDouble();
                                    } catch (InputMismatchException e) {
                                        view.exception("not float");
                                    }
                                }
                                String query = "INSERT INTO Product(ProductName) VALUES (" + name + ");";
                                db.insert(dbQuery);
                                String newId = db.insert.toString();
                                query = "INSERT INTO Price VALUES (" + price + ") WHERE ProductId = " + newId + ";";
                                db.insert(dbQuery);
                                view.out(db.insert.result.toString());
                                break;
                            //change products; function_set name & set price to a user input productID
                            case 2:
                                view.changeproduct();
                                int idchange = SN.nextInt(); 
                                while (idchange < 1) {
                                    try {
                                        idchange = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                view.productSetName();
                                name = SN.nextLine();
                                if (SN.hasNext()) {
                                    String tmp = SN.next();
                                } 
                                view.productSetPrice();
                                price = -1f;
                                while (price < 0) {
                                    try {
                                        price = SN.nextDouble();
                                    } catch (InputMismatchException e) {
                                        view.exception("not float");
                                    }
                                }
                                query = "UPDATE Product SET ProductName =" + name + "WHERE ProductId = " + idchange + ";";
                                db.update(dbQuery);
                                query = "UPDATE Price SET price =" + price +" WHERE ProductId = " + idchange + ";";
                                db.update(dbQuery);
                                view.out(db.update.result.toString());
                                break;
                            //delete products;function_ delect product by user input productID
                            case 3:
                                view.productDeleteProduct();
                                int iddelete = SN.nextInt();
                                while (iddelete < 1) {
                                    try {
                                        iddelete = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                query = "DELETE Product WHERE ProductId = " + iddelete + ";";
                                db.delete(dbQuery);
                                query = "DELETE Price WHERE ProductId = " + iddelete + ";";
                                db.delete(dbQuery);
                                view.out(db.delete.result.toString());
                        }
                    }
                    a2 = -1;
                    break;
                    
                //manage employees
                case 2:
                    while (a2 != 0) {
                        view.manageEmployees();
                        try {
                            a2 = SN.nextInt();
                        } catch (InputMismatchException e) {
                            view.exception("not integer");
                        }
                        switch (a2) {
                            //add employees;function_set name & set phone
                            case 1:      
                                view.employeeSetName();
                                String name = SN.nextLine();
                                if (SN.hasNext()) {
                                    String tmp = SN.next();
                                }
                                view.employeeSetPhone();
                                int phone = SN.nextInt();
                                
                               //problem: how the computer knows that the phone number is appropriate? for example, user input "42"
                                while (phone < 0) {
                                    try {
                                        phone = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                String query = "INSERT INTO Employees(EmployeeName) VALUES (" + name + ");";
                                db.insert(dbQuery);
                                String newId = db.insert.toString();
                                query = "INSERT INTO Employees VALUES (" + phone + ") WHERE ProductId = " + newId + ";";
                                db.insert(dbQuery);
                                view.out(db.insert.result.toString());
                                break;
                             //change employees;function_ set name & set phone to a user input employeeID
                             case 2:
                                view.changeemployee();
                                int idchange = SN.nextInt(); 
                                while (idchange < 1) {
                                    try {
                                        idchange = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                view.employeeSetName();
                                name = SN.nextLine();
                                if (SN.hasNext()) {
                                    String tmp = SN.next();
                                }
                                view.employeeSetPhone();
                                phone = SN.nextInt();
                                while (phone < 0) {
                                    try {
                                        phone = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                query = "UPDATE Employees SET EmployeeName =" + name + ",PhoneNumber=" + phone + "WHERE EmployeeID = " + idchange + ";";
                                db.update(dbQuery);
                                view.out(db.update.result.toString());
                                break;
                            //delete employee;function_ delect employee by user input employeeID
                            case 3:
                                view.empolyeeDeleteemployee();
                                int iddelete = SN.nextInt();
                                while (iddelete < 1) {
                                    try {
                                        iddelete = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                query = "DELETE Employees WHERE EmployeeID = " + iddelete + ";";
                                db.delete(dbQuery);
                                view.out(db.delete.result.toString());
                         }
                    }
                    a2 = -1;
                    break;
                    
               //manage orders 
               case 3:
                    while (a2 != 0) {
                        view.manageOrders();
                        try {
                            a2 = SN.nextInt();
                        } catch (InputMismatchException e) {
                            view.exception("not integer");
                        }
                        switch (a2) {
                            //set employee to order
                            case 1:
                                view.orderSetEmployee_order();
                                int orderid = SN.nextInt();
                                while (orderid < 1) {
                                    try {
                                        orderid = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                view.orderSetEmployee_employee();
                                double price = -1f;
                                int employeeid = SN.nextInt();
                                while (employeeid < 1) {
                                    try {
                                        employeeid = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                String query = "INSERT INTO OrderExecutor(OrderID) VALUES (" + orderid + ");";
                                db.insert(dbQuery);
                                query = "INSERT INTO EmployeeID VALUES (" + employeeid + ") WHERE OrderID = " + orderid + ";";
                                db.insert(dbQuery);
                                view.out(db.insert.result.toString());
                                break;
                             //delete order 
                             case 3:
                                view.orderDeleteOrder();
                                orderid = SN.nextInt();
                                while (orderid < 1) {
                                    try {
                                        orderid = SN.nextInt();
                                    } catch (InputMismatchException e) {
                                        view.exception("not int");
                                    }
                                }
                                query = "DELETE OrderExecutor WHERE OrderID = " + orderid + ";";
                                db.delete(dbQuery);
                                view.out(db.delete.result.toString());
                                break;
                          /*    
                          for get daily/monthly/annual statistic, 
                          some difficulties in pick up the day/month/year in the daytime type of value 
                             
                              case 4:
                              case 5:
                              case 6:
                                 
                                 */
                        }
                   }
                    a2 = -1;
                    break;
        }
        /*
        
        // SELECT
        try {
        // Call Model
        db.select(dbQuery);
        // Call View
        view.printArray(db.select.resultArray);
        } catch (Exception e) {
        out(e.getMessage());
        }
         * 
         */
        db.close();


    }
}
}
